const csv = require("csv-parser");
const sqlQuery = require("../db/db");
const {
  insertWordAndPronSql,
  hasWordSql,
  getWordIdSql,
  hasWordMeaningSql,
  getInsertDefinitionSql,
} = require("../db/sql");
const fs = require("fs");
const path = require("path");

async function hasWord(word) {
  const wordList = await sqlQuery(hasWordSql, word);
  return wordList?.length > 0;
}

const format = {
  word(word) {
    const needFormat = /^[-']/.test(word);
    return needFormat ? word.slice(1) : word;
  },
  phonetic(phonetic) {
    if (!phonetic) return "";
    const hasMorePrefix = /^[,]/.test(phonetic);
    return hasMorePrefix ? `/${phonetic.slice(1)}/` : `/${phonetic}/`;
  },
  definition(translation) {
    if (!translation) return false;
    //转换为数据库前缀
    const part_of_speechs = [
      ["n.", "noun"],
      ["adv.", "adv"],
      ["v.", "verb"],
      ["vt.", "verb"],
      ["a.", "adj"],
    ];

    // 解析:一个单词的不同词性释义用 \\n 分隔
    const speechList = translation.split("\\n");
    const res = speechList.reduce((pre, cur) => {
      const prefix = part_of_speechs.find(([key]) => cur.startsWith(key));
      if (cur.length >= 255) return pre;
      if (prefix) {
        const [key, val] = prefix;
        const def = cur.slice(key.length);
        pre[`${val}_definition`] = def.replaceAll('"', "'");
      } else {
        pre[`unknown_definition`] = cur.replaceAll('"', "'");
      }
      return pre;
    }, {});
    return Object.keys(res).length > 0 ? res : false;
  },
};

module.exports = function handleCsvFile() {
  const start = performance.now();
  let count = 0;
  fs.createReadStream(path.resolve(__dirname, "csv/z.csv"))
    .pipe(csv())
    .on("data", async (data) => {
      // const word = format.word(data.word);
      const word = data.word;
      if (!(await hasWord(word))) return;
      const [{ id }] = await sqlQuery(getWordIdSql, word);
      const hasMeaning = await sqlQuery(hasWordMeaningSql, id);
      // console.log(
      //   hasMeaning.length > 0
      //     ? `已经存在数据了---${word}`
      //     : `准备添加数据111111111${word}`
      // );
      if (hasMeaning.length > 0) return;
      const defs = format.definition(data.translation);
      if (!defs) return;
      const insertSql = getInsertDefinitionSql(defs);
      console.log(
        `已用时${
          (performance.now() - start) / 1000
        }秒, 单词数为:${++count}  单词:${word}`,
        defs
      );

      await sqlQuery(insertSql, word);
    });
};
